﻿using MySql.Data.MySqlClient;
using System;

namespace Library
{
    class BookSql
    {
        private int bookid, booknum, booknownum, booklendnum, bookleftnum, sum;
        private string bookname, bookprice;
        private string url = "server=192.168.99.57;port=3306;user=root;password=123456;database=book;Charset=utf8;SslMode=None;";
        private string sql = "";
        private MySqlDataReader reader = null;
        private Debug debug = new Debug();

        //查询操作
        //作用：先进行一次查询，检查当前书本是否存在图书馆书库中,true为存在,false为否

        public bool queryBook(string bookname)
        {
            MySqlConnection mySqlConnection = new MySqlConnection(url);
            bool sign = true;
            try
            {
                mySqlConnection.Open();
                sql = "select * from bookinfo where bookname='" + bookname + "';";
                MySqlCommand mySqlCommand = new MySqlCommand(sql, mySqlConnection);
                reader = mySqlCommand.ExecuteReader();
                if (reader.Read())
                {
                    sign = true;
                    this.bookid = reader.GetInt32(0);
                    this.bookname = reader.GetString(1);
                    this.bookprice = reader.GetString(2);
                    this.booknum = reader.GetInt32(3);
                    this.booknownum = reader.GetInt32(4);
                    this.booklendnum = reader.GetInt32(5);
                    this.bookleftnum = reader.GetInt32(6);
                }
                else
                {
                    sign = false;
                }
                mySqlConnection.Close();
            }
            catch
            {
                mySqlConnection.Close();
            }
            finally
            {
                mySqlConnection.Close();
            }
            return sign;
        }

        public void queryAll()
        {
            MySqlConnection mySqlConnection = new MySqlConnection(url);
            try
            {
                mySqlConnection.Open();
                sql = "select count(*) from bookinfo;";
                MySqlCommand mySqlCommand = new MySqlCommand(sql, mySqlConnection);
                reader = mySqlCommand.ExecuteReader();
                while (reader.Read())
                    this.sum = reader.GetInt32(0);
                mySqlConnection.Close();
            }
            catch
            {
                mySqlConnection.Close();
            }
            finally
            {
                mySqlConnection.Close();
            }
        }
        //增改操作
        //增加：捐赠图书模块[addBook()]
        //修改：借阅图书、归还图书、挂失图书[changeBook()]

        public int addBook(string bookname,int booknum)
        {
            int result = 0;
            //result=0,在原有的库存上加一,result=1,新书
            if (queryBook(bookname) == true)
            {
                result = 0;
                sql = "update bookinfo set booknownum='" + (this.booknownum + booknum) + "',booknum='"+(this.booknum+booknum)+"' where bookname='" + this.bookname + "';";
                //sql = "insert into bookinfo(booknum,booknownum) values(" + (this.booknum + 1) + "," + this.booknownum + ");";
            }
            else
            {
                queryAll();
                sql = "insert into bookinfo values('" + (this.sum + 1) + "' , '" + bookname + "' , " + "'捐赠(无价)'" + " , '"+booknum+"','" + booknum + "' , '" + this.booknownum + "' , '" + this.booklendnum + "');";
            }
            debug.console(sql);


            MySqlConnection mySqlConnection = new MySqlConnection(url);
            try
            {
                mySqlConnection.Open();
                MySqlCommand mySqlCommand = new MySqlCommand(sql, mySqlConnection);
                result = mySqlCommand.ExecuteNonQuery();
                mySqlConnection.Close();
            }
            catch
            {
                mySqlConnection.Close();
            }
            finally
            {
                mySqlConnection.Close();
            }
            return result;
        }

        public int changeBook(string bookname, int mode)
        {
            //mode=1 借阅
            //mode=2 归还
            int sign = 0;
            if (queryBook(bookname))
            {
                if (this.booknownum > 0)
                {
                    sign = 1;
                }
            }
            //TODO bug
            //if (sign == 1)
            if(mode==1)
            {
                //if (mode == 1)
                if(sign==1)
                {
                    if (this.booknownum > 0)
                    {
                        sql = "update bookinfo set booknownum='" + (this.booknownum - 1) + "' , booklendnum='" + (this.booklendnum + 1) + "' where bookname='" + bookname + "' ;";
                        sign = 2;
                    }
                    else
                    {
                        sign = -2;
                    }
                }
            }
            else if (mode == 2)
            {
                if (this.booklendnum > 0)
                {

                    sql = "update bookinfo set booknownum='" + (this.booknownum + 1) + "' , booklendnum='" + (this.booklendnum - 1) + "' where bookname='" + bookname + "' ;";
                    sign = 3;
                }
                else
                {
                    sign = -3;
                }
            }
            debug.console(sql);
            MySqlConnection mySqlConnection = new MySqlConnection(url);
            try
            {
                Console.WriteLine(sql);
                mySqlConnection.Open();
                MySqlCommand mySqlCommand = new MySqlCommand(sql, mySqlConnection);
                mySqlCommand.ExecuteNonQuery();
                mySqlConnection.Close();
            }
            catch
            {
                mySqlConnection.Close();
            }
            finally
            {
                mySqlConnection.Close();
            }
            return sign;
        }

        public int lossBook(string bookname)
        {
            int sign = 0;
            if (queryBook(bookname))
            {
                if (this.booknownum > 0)
                {
                    sign = 1;
                }
            }
            if (sign == 1)
            {
                if (this.booknownum > 0)
                {
                    if (this.bookleftnum > 0)
                    {
                        sql = "update bookinfo set booknownum='" + (this.booknownum - 1) + "' where bookname='" + bookname + "';";
                        MySqlConnection mySqlConnection = new MySqlConnection(url);
                        try
                        {
                            mySqlConnection.Open();
                            MySqlCommand mySqlCommand = new MySqlCommand(sql, mySqlConnection);
                            mySqlCommand.ExecuteNonQuery();
                            mySqlConnection.Close();
                        }
                        catch
                        {
                            mySqlConnection.Close();
                        }
                        finally
                        {
                            mySqlConnection.Close();
                        }
                    }
                    else
                    {
                        sign = -1;
                    }
                }
                else
                {
                    sign = -2;
                }
            }
            return sign;
        }

        public string[] getBookInfo()
        {
            string[] infos = new string[13];
            infos[0] = Convert.ToString(this.bookid);
            infos[1] = this.bookname;
            infos[2] = this.bookprice;
            infos[3] = Convert.ToString(this.booknum);
            infos[4] = Convert.ToString(this.booklendnum);
            infos[5] = Convert.ToString(this.booknownum);
            infos[6] = Convert.ToString(this.bookleftnum);
            return infos;

        }

        public string tostring()
        {
            string [] tmp=getBookInfo();
            string s = "";
            for(int i = 0; i < 7; i++)
            {
                s += tmp[i];
            }
            return s;
        }
    }
}
